﻿using System.Data.SqlClient;
using System.Data;
using System.Threading;
using System.Windows.Forms;


namespace DATA_SERVER
{
    public abstract class DB_SQLServer
    {
        private static int b_lock = 0;
        public static int Status = 0;
        public static bool b_Init;

        //初始化远程数据库，创建不存在的表
        public static bool Init()
        {
            b_Init = false;
            try
            {
                ExecuteNonQuery("IF OBJECT_ID('T_PP_AccessControl','U') IS NULL CREATE TABLE [T_PP_AccessControl]([ID] INT IDENTITY(1,1),[StaffNo] VARCHAR(20),[Time] DATETIME,[BeaMac] VARCHAR(32),CONSTRAINT [PK_T_PP_AccessControl] PRIMARY KEY CLUSTERED ([ID] ASC))");
                ExecuteNonQuery("IF OBJECT_ID('T_SM_StaffExtra','U') IS NULL CREATE TABLE [T_SM_StaffExtra]([Pin] VARCHAR(20),[Type] VARCHAR(10),[Extra] TEXT,[ModifyTime] DATETIME,CONSTRAINT [PK_T_SM_StaffExtra] PRIMARY KEY CLUSTERED ([Pin] ASC))");
                b_Init = true;
            }
            catch { }
            return b_Init;
        }

        //填充SqlCommand
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string cmdText)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandTimeout = 5;
                Status = 1;
            }
            catch
            {
                Status = -1;
                throw;
            }
        }

        //执行数据库命令，并返回单个int值
        public static int ExecuteNonQuery(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(Global.DBConnectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        //执行数据库命令，并返回reader
        public static SqlDataReader ExecuteReader(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                SqlConnection conn = new SqlConnection(Global.DBConnectionString);

                try
                {
                    PrepareCommand(cmd, conn, cmdText);
                    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Close();
                    throw;
                }
            }
        }

        //执行数据库命令，并返回单个object
        public static object ExecuteScalar(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(Global.DBConnectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        //执行数据库命令，并返回DataTable
        public static DataTable ExecuteDataTable(string cmdText)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                using (SqlConnection conn = new SqlConnection(Global.DBConnectionString))
                {
                    PrepareCommand(cmd, conn, cmdText);

                    using (SqlDataAdapter thisAdapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();

                        try
                        {
                            thisAdapter.Fill(dt);
                            thisAdapter.Dispose();
                        }
                        catch { }
                        finally
                        {
                            if (conn.State == ConnectionState.Open)
                            {
                                conn.Dispose();
                            }
                        }

                        return dt;
                    }
                }
            }
        }

        //向门禁机同步数据
        public static void Synchronize(object obj)
        {
            if (MessageBox.Show("是否先清除已有用户信息？", "同步用户信息", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) == DialogResult.Yes)
            {
                ClearUser(obj);
            }
                
            if (Interlocked.Exchange(ref b_lock, 1) == 0)
            {
                Service myService = obj as Service;
                Data_Func.ShowText("同步开始！");
                try
                {
                    int count = 0;
                    DataTable dt = DB_SQLServer.ExecuteDataTable("SELECT s.[StaffNo], t.[TagMac], s.[StaffName], sd.DEPTNAME, tst.Typejobname, tsp.PositionsName, s.[StaffPhotoPath], t.[NFC], e.[Type], e.[Extra] FROM[T_SM_Staff] s LEFT JOIN[T_JC_Tag] t ON t.[StafffID] = s.[StaffID] LEFT JOIN[T_SM_StaffExtra] e ON t.[TagMac] = e.[Pin] LEFT JOIN SYS_DEPT sd on sd.DEPTID = s.DeptID LEFT JOIN T_SM_TypeJob tst on tst.TypeJobID = s.TYPEJOB LEFT JOIN T_SM_Position tsp on tsp.PositionsID = s.PositionsID WHERE s.[STATUS] = 'YGZT001'");

                    for (int i = 0; i < dt.Rows.Count; i++)
                    //for (int i = 0; i < 20; i++)
                    {
                        try
                        {
                            if (Global.UseTag && (dt.Rows[i][1].ToString() == ""))
                            {
                                continue;
                            }
                            myService.AddUser(Global.UseTag ? dt.Rows[i][1].ToString() : dt.Rows[i][0].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][7].ToString(), dt.Rows[i][8].ToString(), dt.Rows[i][9].ToString());
                            DB_SQLite.ExecuteNonQuery(string.Format("REPLACE INTO [T_SM_Staff] ([Pin],[StaffNo],[TagMac],[StaffName],[DeptName],[TypeJobName],[PositionsName],[StaffPhotoPath]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", Global.UseTag ? dt.Rows[i][1] : dt.Rows[i][0], dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt.Rows[i][4], dt.Rows[i][5], dt.Rows[i][6]));
                            //DB_SQLite.ExecuteNonQuery(string.Format("REPLACE INTO [T_SM_StaffExtra] ([Pin],[Type],[Extra]) VALUES ('{0}','{1}','{2}')", Global.UseTag ? dt.Rows[i][1] : dt.Rows[i][0], dt.Rows[i][8], dt.Rows[i][9]));
                            count++;
                        }
                        catch { }
                    }
                    if (count > 0)
                    {
                        Data_Func.ShowText("（手动）用户信息已从远程服务器批量同步至设备数据库共" + count + "条！");
                    }
                }
                catch
                {
                    if (Status > 0)
                    {
                        Data_Func.ShowText("用户信息同步失败！");
                    }
                    else
                    {
                        Data_Func.ShowText("远程数据库连接失败！");
                        Interlocked.Exchange(ref b_lock, 0);
                        return;
                    }
                }
                /*-------------------------------------------------------------------------------------------*/
                Data_Func.ShowText("同步结束！");
                Interlocked.Exchange(ref b_lock, 0);
            }
            else
            {
                MessageBox.Show("正在执行其他命令！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
        }

        //清空用户
        public static void ClearUser(object obj)
        {
            if (Interlocked.Exchange(ref b_lock, 1) == 0)
            {
                Service myService = obj as Service;
                myService.ClearUser();
                Data_Func.ShowText("清除完成！");
                Interlocked.Exchange(ref b_lock, 0);
            }
            else
            {
                MessageBox.Show("正在执行其他命令！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
        }
    }

    
}
